package com.example.ems.dao;

import com.example.ems.bean.Department;
import com.example.ems.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDAO {
    public List<Department> getAllDepartments() {
        List<Department> departments = new ArrayList<>();
        // SQL 查询需要 JOIN employees 表来获取负责人姓名
        String sql = "SELECT d.*, e.name as manager_name " +
                "FROM departments d " +
                "LEFT JOIN employees e ON d.manager_id = e.id " +
                "ORDER BY d.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                Department dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                // 处理可能为null的manager_id
                int managerId = rs.getInt("manager_id");
                if (!rs.wasNull()) { // 检查 manager_id 是否为 SQL NULL
                    dept.setManagerId(managerId);
                    dept.setManagerName(rs.getString("manager_name"));
                } else {
                    dept.setManagerId(null); // 明确设置为 null
                    dept.setManagerName(null); // 或显示 "无"
                }
                departments.add(dept);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return departments;
    }

    public Department getDepartmentById(int id) {
        Department dept = null;
        String sql = "SELECT d.*, e.name as manager_name " +
                "FROM departments d " +
                "LEFT JOIN employees e ON d.manager_id = e.id " +
                "WHERE d.id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    dept = new Department();
                    dept.setId(rs.getInt("id"));
                    dept.setName(rs.getString("name"));
                    int managerId = rs.getInt("manager_id");
                    if (!rs.wasNull()) {
                        dept.setManagerId(managerId);
                        dept.setManagerName(rs.getString("manager_name"));
                    } else {
                        dept.setManagerId(null);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dept;
    }

    public boolean addDepartment(Department department) {
        String sql = "INSERT INTO departments (name, manager_id) VALUES (?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, department.getName());
            if (department.getManagerId() != null && department.getManagerId() > 0) { // 确保 managerId 有效
                pstmt.setInt(2, department.getManagerId());
            } else {
                pstmt.setNull(2, Types.INTEGER); // 如果没有选择负责人，则插入 NULL
            }
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean updateDepartment(Department department) {
        String sql = "UPDATE departments SET name = ?, manager_id = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, department.getName());
            if (department.getManagerId() != null && department.getManagerId() > 0) {
                pstmt.setInt(2, department.getManagerId());
            } else {
                pstmt.setNull(2, Types.INTEGER);
            }
            pstmt.setInt(3, department.getId());
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean deleteDepartment(int id) {
        String sql = "DELETE FROM departments WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    // 在 DepartmentDAO.java 中添加
    public List<Department> getDepartmentsByName(String searchName) {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT d.*, e.name as manager_name " +
                "FROM departments d " +
                "LEFT JOIN employees e ON d.manager_id = e.id " +
                "WHERE d.name LIKE ? " + // 使用 LIKE 进行模糊查询
                "ORDER BY d.name";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, "%" + searchName + "%"); // 关键：前后加%实现模糊匹配
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    Department dept = new Department();
                    dept.setId(rs.getInt("id"));
                    dept.setName(rs.getString("name"));
                    int managerId = rs.getInt("manager_id");
                    if (!rs.wasNull()) {
                        dept.setManagerId(managerId);
                        dept.setManagerName(rs.getString("manager_name"));
                    } else {
                        dept.setManagerId(null);
                        dept.setManagerName(null);
                    }
                    departments.add(dept);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return departments;
    }

}
